# -- coding: utf-8 --

import MySQLdb
import requests
import re
import json
import MySQLdb.cursors


def get_vid(url):
    response = requests.get(url)
    html = response.text
    href = re.search(r'<link rel="canonical" href="(.*?)"',html).group(1)
    ht = href.split('/')[-1]
    vid = re.search(r'(.*?).html', ht).group(1)
    return vid

def get_info_url(vid):

    # 默认的参数
    json_type='json'
    platform = '11001'
    guid='ba7f7fab8f8aef2c4ae45883f221c04d'
    sdtfrom = 'v1103'
    _qv_rmt='703hXQMOA1937593I%3D'
    _qv_rmt2='egp3C2aj149060UUA%3D'
    # getinfo的base_url
    base_info_url = 'https://h5vv.video.qq.com/getinfo?vid={vid}&otype={otype}&guid={guid}&platform=\
    {platform}&sdtfrom={sdtfrom}&_qv_rmt={_qv_rmt}&_qv_rmt2={_qv_rmt2}'
    # format方法生成getinfo
    info_url = base_info_url.format(vid=vid, otype=json_type, guid=guid, platform=platform, \
                                    sdtfrom=sdtfrom, _qv_rmt=_qv_rmt, _qv_rmt2=_qv_rmt2)
    return info_url

def get_keys(info_url):
    response = requests.get(info_url)
    html = response.text
    # 正则表达式提取json数据并序列化为json格式
    data = re.search('=(.*);', html).group(1)
    data = json.loads(data)
    for each in data.get('vl').get('vi'):
        fvkey = each.get('fvkey')
        fn = each.get('fn')
        self_host = each.get('ul').get('ui')[0].get('url')

        keys={
            'fvkey':fvkey,
            'fn':fn,
            'self_host':self_host
        }
        return keys

def get_real_url(keys):
    # 真实地址的base_url
    base_real_url = '{self_host}{fn}?vkey={fvkey}'
    # 组装视频真实地址
    real_url = base_real_url.format(self_host=keys['self_host'],fn=keys['fn'], fvkey=keys['fvkey'])
    return real_url

# 从数据库中获取当前视频地址
def get_current_video_url():
    mysql_host = "rm-wz9326c6mt06wsrigo.mysql.rds.aliyuncs.com"
    mysql_user = "root_zjx"
    mysql_passwd = "zjx285831!@#"
    db = MySQLdb.connect(host=mysql_host, user=mysql_user,passwd=mysql_passwd,cursorclass = MySQLdb.cursors.DictCursor, charset="utf8")
    cursor = db.cursor()
    cursor.execute("use `%s`;" % "smart_home")
    sql = "select id,media_url from lure_media_detail;"
    count = cursor.execute(sql)
    rows = cursor.fetchall()
    cursor.close()
    db.close()
    return rows

def url_to_vid(rows):
    media_vids = {}
    for row in rows:
        print row['id'].__str__()+": "+row['media_url']
        media_url = row['media_url']
        media_vid_index = media_url.find(".mp4")
        vid = media_url[0:media_vid_index].split('/')[-1]
        if vid.find('.') > 0:
            vid = vid.split('.')[0]
        media_vids[row['id']] = vid
    return media_vids

def main():
    # # url = input('请输入视频链接:')
    # url = 'https://v.qq.com/x/page/c0334eajum1.html'
    # vid = get_vid(url)
    # print "vid="+ vid
    # info_url = get_info_url(vid)
    # keys = get_keys(info_url)
    # real_url = get_real_url(keys)
    # print keys

    # print('视频真实地址为:', real_url)
    rows = get_current_video_url()
    media_vids = url_to_vid(rows)
    # print media_vids
    _key_direct = {}
    for key in media_vids:
        info_url = get_info_url(media_vids[key])
        keys = get_keys(info_url)
        # real_url = get_real_url(keys)
        # print keys
        _key_direct[key] = keys['fvkey']
    print _key_direct
    print rows
    # 链接数据库
    mysql_host = "rm-wz9326c6mt06wsrigo.mysql.rds.aliyuncs.com"
    mysql_user = "root_zjx"
    mysql_passwd = "zjx285831!@#"
    db = MySQLdb.connect(host=mysql_host, user=mysql_user,passwd=mysql_passwd,cursorclass = MySQLdb.cursors.DictCursor, charset="utf8")
    cursor = db.cursor()
    cursor.execute("use `%s`;" % "smart_home")

    # 更新新的key
    for key in _key_direct:
        for row in rows:
            if row['id'] == key:
                media_url = row['media_url']
                vkey_index = media_url.find('vkey=')
                media_url = media_url[0:vkey_index+5]+_key_direct[key]
                sql = "UPDATE lure_media_detail SET media_url = '%s' WHERE id =%d" % (media_url,key)
                try:
                    print sql
                    n = cursor.execute(sql)
                    print n
                    db.commit()
                except Exception,e:
                    # Rollback in case there is any error
                    print Exception,":",e
                    print "mysql save failed %s" % media_url
                    db.rollback()
    # 关闭数据库连接
    cursor.close()
    db.close()

if __name__ == '__main__':
    main()
